﻿SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetArticle_PreviousNext]
	@articleID int
AS
	/* SET NOCOUNT ON */
DECLARE @postTime DateTime,@catID int
SELECT @postTime= log_postTime,@catID=log_catID
FROM [dbo].[blog_Article]
WHERE log_id = @articleID

SELECT * FROM
(
	SELECT Top 1 cat_id as ID,cat_name as title,@postTime as postTime FROM blog_Category
	WHERE cat_id = @catID
) HOME
UNION ALL
SELECT * FROM
(
	SELECT Top 1 log_id as ID,log_title,log_posttime as title
		 FROM blog_Article
	WHERE log_postTime <= @postTime
		AND log_id != @articleID
		ORDER BY log_posttime DESC
) [Previous]
UNION ALL
SELECT log_id,log_title,log_posttime FROM
(
	SELECT Top 1 * FROM blog_Article
	WHERE log_postTime >= @postTime
		AND log_id != @articleID
		ORDER BY log_posttime ASC
) [Next]



GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'GetArticle_PreviousNext'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateGlobalCounter]

AS
	DECLARE @artCount int,@commentCount int,@userCount int
SET @artCount=(select count(*) from blog_Article)
SET @commentCount=(select count(*) from blog_Comment)
SET @userCount=(select count(*) from blog_User)

UPDATE blog_Settings SET set_value0 =@artCount  WHERE set_name='counterArticle'
UPDATE blog_Settings SET set_value0 =@commentCount  WHERE set_name='counterComment'
UPDATE blog_Settings SET set_value0 =@userCount  WHERE set_name='counterUser'
	
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'UpdateGlobalCounter'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateCategoryCount]
	@catID int
AS
	/* SET NOCOUNT ON */
	RETURN
Delete from blog_Comment where log_id in (SELECT log_id From blog_article where log_catID=@catID)
Delete from blog_Trackback where log_id in (SELECT log_id From blog_article where log_catID=@catID)
Delete from blog_article where log_catID=@catID

DECLARE @artCounter int,@commCount int,@trackbackCount int
SET @artCounter=(select count(log_id) from blog_Article)
SET @commCount=(select count(comm_id) from blog_Comment)
SET @trackbackCount=(SELECT count(tb_id) FROM [blog_Trackback])

UPDATE blog_Settings SET set_value0 =@artCounter WHERE set_name='counterArticle'
UPDATE blog_Settings SET set_value0 =@commCount WHERE set_name='counterComment'
UPDATE blog_Settings SET set_value0 =@trackbackCount WHERE set_name='counterTrackback'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'UpdateCategoryCount'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateArticleCount]
    @oldCatID int,
	@catID int,
	@authorID int
AS
	DECLARE @oldcatCounter int,@catCounter int,@artCount int,@userCount int
SET @oldcatCounter=(select count(log_id) from blog_Article WHERE log_catid=@oldCatID)
SET @catCounter=(select count(log_id) from blog_Article WHERE log_catid=@catID)
SET @userCount=(select count(log_id) from blog_Article WHERE log_authorID=@authorID) 
SET @artCount=(select count(log_id) from blog_Article)
if(@oldCatID!=@catID)
begin
UPDATE blog_Category SET cat_articleCount =@oldcatCounter  WHERE cat_id=@oldCatID
end
UPDATE blog_Category SET cat_articleCount =@catCounter  WHERE cat_id=@catID
UPDATE blog_User SET user_articleCount =@userCount  WHERE user_id=@authorID
UPDATE blog_Settings SET set_value0 =@artCount  WHERE set_name='counterArticle'
	RETURN

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'UpdateArticleCount'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateCommentCount]
	@logID int,
	@authorID int
AS
DECLARE @artcommCounter int,@commCount int
SET @artcommCounter=(select count(log_id) from blog_Comment WHERE log_id=@logID)
SET @commCount=(select count(comm_id) from blog_Comment)

UPDATE blog_Article SET log_commentCount =@artcommCounter WHERE log_id=@logID

if(@authorID>0)
DECLARE @userCount int
SET @userCount=(select count(comm_id) from blog_Comment WHERE comm_authorID=@authorID)
UPDATE blog_User SET user_commentCount =@userCount  WHERE user_id=@authorID

UPDATE blog_Settings SET set_value0 =@commCount WHERE set_name='counterComment'

RETURN

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'UpdateCommentCount'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetArticleCountByCatID]
@catID int
AS
	SET NOCOUNT ON;
	if @catID>=0
SELECT COUNT(log_id) FROM blog_Article WHERE log_catID=@catID
    else
SELECT COUNT(log_id) FROM blog_Article   
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'GetArticleCountByCatID'
